package br.com.bancodecurriculos.pessoa.comum.dao.impl;

import java.io.IOException;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

import br.com.bancodecurriculos.pessoa.comum.dao.IEnderecoDAOHelper;
import br.com.bancodecurriculos.pessoa.comum.dominio.Endereco;
import br.com.bancodecurriculos.pessoa.comum.dominio.Endereco.TipoEndereco;
import br.com.bancodecurriculos.util.dao.JDBCConnectionFactory;
import br.com.bancodecurriculos.util.exception.RegistroExistenteException;
import br.com.bancodecurriculos.util.exception.RegistroInexistenteException;

public class EnderecoDAOHelper implements IEnderecoDAOHelper {

	/*
	 * private static final String SQL_TIPO_ENDERECO_GET_BY_NAME =
	 * "tipoendereco.getByName"; private static final String
	 * SQL_TIPO_ENDERECO_GET_ALL = "tipoendereco.getAll";
	 * 
	 * private static final String SQL_ENDERECO_INSERT = "endereco.insert";
	 * private static final String SQL_ENDERECO_UPDATE = "endereco.update";
	 */

	private static final String SQL_TIPO_ENDERECO_GET_BY_NAME = "SELECT * FROM tipoendereco WHERE nome = ?";
	private static final String SQL_TIPO_ENDERECO_GET_ALL = "SELECT * FROM tipoendereco";

	private static final String SQL_ENDERECO_INSERT = "INSERT INTO endereco (logradouro, numero, complemento, cep, bairro,"
			+ " cidade, uf) VALUES (?, ?, ?, ?, ?, ?, ?, ?)";
	private static final String SQL_ENDERECO_UPDATE = "UPDATE endereco SET logradouro = ?, numero = ?, complemento = ?, cep = ?,"
			+ " bairro = ?, cidade = ?, uf = ?, id_tipoendereco = ? WHERE id = ?";
	private static final String SQL_ENDERECO_GET_BY_ID = "SELECT endereco.id AS id, endereco.logradouro AS logradouro, endereco.numero AS numero,"
			+ " endereco.complemento AS complemento, endereco.cep AS cep, endereco.bairro AS bairro, endereco.cidade AS cidade,"
			+ " endereco.uf AS uf, endereco.id_tipoendereco AS id_tipoendereco, tipoendereco.nome AS nome_tipoendereco FROM endereco"
			+ " INNER JOIN tipoendereco ON endereco.id_perfil = tipoendereco.id  WHERE endereco.id = ?";

	private static final String SQL_ENDERECO_DELETE = "DELETE FROM endereco WHERE id = ?";

	private static final String ENDERECO_INEXISTENTE_EXCEPTION = "Endere�o inexistente!";

	@Deprecated
	@Override
	public void save(Endereco t) throws ClassNotFoundException, SQLException,
			RegistroExistenteException, IOException {
		// TODO Auto-generated method stub

	}

	@Override
	public long saveReturningId(Endereco t) throws ClassNotFoundException,
			SQLException, RegistroExistenteException, IOException {
		TipoEndereco tipoEndereco = this.getTipoEnderecoByNome(t
				.getTipoEndereco().getNome());
		PreparedStatement preparedStatement = JDBCConnectionFactory
				.getInstance()
				.getConnection()
				.prepareStatement(SQL_ENDERECO_INSERT,
						PreparedStatement.RETURN_GENERATED_KEYS);
		preparedStatement.setString(1, t.getLogradouro());
		preparedStatement.setInt(2, t.getNumero());
		preparedStatement.setString(3, t.getComplemento());
		preparedStatement.setString(4, t.getCep());
		preparedStatement.setString(5, t.getBairro());
		preparedStatement.setString(6, t.getCidade());
		preparedStatement.setString(7, t.getUf());
		preparedStatement.setLong(8, tipoEndereco.getId());
		ResultSet resultSet = preparedStatement.getGeneratedKeys();
		long id = 0;
		if (resultSet.next()) {
			id = resultSet.getLong("id");
		}
		preparedStatement.close();
		resultSet.close();
		return id;
	}

	@Override
	public void update(Endereco t) throws ClassNotFoundException, SQLException,
			RegistroInexistenteException, IOException {
		if (this.getById(t.getId()) != null) {
			PreparedStatement preparedStatement = JDBCConnectionFactory
					.getInstance().getConnection()
					.prepareStatement(SQL_ENDERECO_UPDATE);
			preparedStatement.setString(1, t.getLogradouro());
			preparedStatement.setInt(2, t.getNumero());
			preparedStatement.setString(3, t.getComplemento());
			preparedStatement.setString(4, t.getCep());
			preparedStatement.setString(5, t.getBairro());
			preparedStatement.setString(6, t.getCidade());
			preparedStatement.setString(7, t.getUf());
			preparedStatement.setLong(8, t.getTipoEndereco().getId());
			preparedStatement.setLong(9, t.getId());
			preparedStatement.executeUpdate();
			preparedStatement.close();
		} else {
			throw new RegistroInexistenteException(
					ENDERECO_INEXISTENTE_EXCEPTION);
		}
	}

	@Override
	public Endereco getById(Long id) throws ClassNotFoundException,
			SQLException, IOException {
		Endereco endereco = null;
		PreparedStatement preparedStatement = JDBCConnectionFactory
				.getInstance().getConnection()
				.prepareStatement(SQL_ENDERECO_GET_BY_ID);
		preparedStatement.setLong(1, id);
		ResultSet resultSet = preparedStatement.executeQuery();
		endereco = this.getEnderecos(resultSet).get(0);
		preparedStatement.close();
		resultSet.close();
		return endereco;
	}

	@Override
	public void delete(Long id) throws ClassNotFoundException, SQLException,
			RegistroInexistenteException, IOException {
		if (this.getById(id) != null) {
			PreparedStatement preparedStatement = JDBCConnectionFactory
					.getInstance().getConnection()
					.prepareStatement(SQL_ENDERECO_DELETE);
			preparedStatement.setLong(1, id);
			preparedStatement.executeUpdate();
			preparedStatement.close();
		} else {
			throw new RegistroInexistenteException(
					ENDERECO_INEXISTENTE_EXCEPTION);
		}
	}
	
	@Override
	public TipoEndereco getTipoEnderecoByNome(String nome)
			throws ClassNotFoundException, SQLException, IOException {
		TipoEndereco tipoEndereco = null;
		PreparedStatement preparedStatement = JDBCConnectionFactory
				.getInstance().getConnection()
				.prepareStatement(SQL_TIPO_ENDERECO_GET_BY_NAME);
		preparedStatement.setString(1, nome);
		ResultSet resultSet = preparedStatement.executeQuery();
		Endereco endereco = new Endereco();
		if (resultSet.next()) {
			tipoEndereco = endereco.new TipoEndereco(
					resultSet.getString("nome"));
			tipoEndereco.setId(resultSet.getLong("id"));
		}
		preparedStatement.close();
		resultSet.close();
		return tipoEndereco;
	}

	private List<Endereco> getEnderecos(ResultSet resultSet)
			throws SQLException {
		List<Endereco> enderecos = new ArrayList<Endereco>();
		while (resultSet.next()) {
			Endereco endereco = new Endereco();
			endereco.setId(resultSet.getLong("id"));
			endereco.setLogradouro(resultSet.getString("logradouro"));
			endereco.setNumero(resultSet.getInt("numero"));
			endereco.setComplemento(resultSet.getString("complemento"));
			endereco.setCep(resultSet.getString("cep"));
			endereco.setBairro(resultSet.getString("bairro"));
			endereco.setCidade(resultSet.getString("cidade"));
			endereco.setUf(resultSet.getString("uf"));
			TipoEndereco tipoEndereco = endereco.new TipoEndereco(
					resultSet.getString("nome_tipoendereco"));
			tipoEndereco.setId(resultSet.getLong("id_tipoendereco"));
			endereco.setTipoEndereco(tipoEndereco);
			enderecos.add(endereco);
		}
		return enderecos;
	}

	public List<TipoEndereco> getAllTipoEndereco()
			throws ClassNotFoundException, SQLException, IOException {
		List<TipoEndereco> tipoEnderecos = new ArrayList<TipoEndereco>();
		PreparedStatement preparedStatement = JDBCConnectionFactory
				.getInstance().getConnection()
				.prepareStatement(SQL_TIPO_ENDERECO_GET_ALL);
		ResultSet resultSet = preparedStatement.executeQuery();
		Endereco endereco = new Endereco();
		while (resultSet.next()) {
			TipoEndereco tipoEndereco = endereco.new TipoEndereco(
					resultSet.getString("nome"));
			tipoEndereco.setId(resultSet.getLong("id"));
			tipoEnderecos.add(tipoEndereco);
		}
		return tipoEnderecos;
	}

}
